﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.Web;

    public class AttendEvectionDao
    {
        public bool Back(SqlConnection conn, int evectionId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Evection] ");
            builder.Append("SET ");
            builder.Append("\t[Status] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[EvectionId] = @EvectionId ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] IN (1) ");
            builder.Append("\tAND [Status] = 0 ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@EvectionId", SqlDbType.Int);
            commandParameters[0].Value = evectionId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Confim(SqlConnection conn, int evectionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Evection] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[EvectionId] = @EvectionId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@EvectionId", SqlDbType.Int) };
            commandParameters[0].Value = evectionId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Delete(SqlConnection conn, int evectionId, string userId, bool isAdministrator)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Evection] ");
            builder.Append("WHERE ");
            builder.Append("\t[EvectionId] = @EvectionId ");
            builder.Append("\tAND ");
            builder.Append("\t( ");
            builder.Append("\t\t([UserId] = @UserId AND [Allow] IN(0,2)) ");
            builder.Append("\t\tOR @IsAdministrator = 1 ");
            builder.Append("\t) ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@EvectionId", SqlDbType.Int);
            commandParameters[0].Value = evectionId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool DeleteByDateTime(SqlConnection conn, DateTime beingDate, DateTime endDate)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Evection] ");
            builder.Append("WHERE ");
            builder.Append("\t( ");
            builder.Append("\t\t[EvectionDate1] >= @BeginDate ");
            builder.Append("\t\tAND [EvectionDate1] <= @EndDate ");
            builder.Append("\t) ");
            builder.Append("\tOR ");
            builder.Append("\t( ");
            builder.Append("\t\t[EvectionDate2] >= @BeginDate ");
            builder.Append("\t\tAND [EvectionDate2] <= @EndDate ");
            builder.Append("\t) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            commandParameters[0].Value = beingDate;
            commandParameters[1] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            commandParameters[1].Value = endDate;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, AttendEvectionData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Attend_Evection] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[LeaderId], ");
            builder.Append("\t[EvectionDest], ");
            builder.Append("\t[EvectionDate1], ");
            builder.Append("\t[EvectionDate2], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Allow], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[NotReason] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@LeaderId, ");
            builder.Append("\t@EvectionDest, ");
            builder.Append("\t@EvectionDate1, ");
            builder.Append("\t@EvectionDate2, ");
            builder.Append("\t@Status, ");
            builder.Append("\t@Allow, ");
            builder.Append("\t@Reason, ");
            builder.Append("\t@NotReason ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[9];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@LeaderId", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.LeaderId;
            commandParameters[2] = new SqlParameter("@EvectionDest", SqlDbType.NText);
            if (data.EvectionDestIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.EvectionDest;
            }
            commandParameters[3] = new SqlParameter("@EvectionDate1", SqlDbType.DateTime);
            commandParameters[3].Value = data.EvectionDate1;
            commandParameters[4] = new SqlParameter("@EvectionDate2", SqlDbType.DateTime);
            commandParameters[4].Value = data.EvectionDate2;
            commandParameters[5] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[5].Value = data.Status;
            commandParameters[6] = new SqlParameter("@Allow", SqlDbType.Int);
            commandParameters[6].Value = data.Allow;
            commandParameters[7] = new SqlParameter("@Reason", SqlDbType.NText);
            if (data.ReasonIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Reason;
            }
            commandParameters[8] = new SqlParameter("@NotReason", SqlDbType.NText);
            if (data.NotReasonIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.NotReason;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool Reason(SqlConnection conn, int evectionId, string notReason)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Evection] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = 2, ");
            builder.Append("\t[NotReason] = @NotReason ");
            builder.Append("WHERE ");
            builder.Append("\t[EvectionId] = @EvectionId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@EvectionId", SqlDbType.Int);
            commandParameters[0].Value = evectionId;
            commandParameters[1] = new SqlParameter("@NotReason", SqlDbType.NText);
            commandParameters[1].Value = notReason;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectAdmin(SqlConnection conn)
        {
            AttendParaDao dao = new AttendParaDao();
            dao.GetPara();
            string attendManager = dao.AttendManager;
            if (("," + attendManager + ",").IndexOf("," + HttpContext.Current.User.Identity.Name + ",") == -1)
            {
                return new DataTable();
            }
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Evection].*, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Evection], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Evection].[Allow] = 0 ");
            builder.Append("\tAND [Attend_Evection].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Evection].[EvectionId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Attend_Evection";
            return set.Tables[0];
        }

        public DataTable SelectBack(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Evection].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Evection], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Evection].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Evection].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND [Attend_Evection].[Allow] IN(1) ");
            builder.Append("\tAND [Attend_Evection].[Status] IN(0) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Evection].[EvectionId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Evection";
            return set.Tables[0];
        }

        public DataTable SelectByEvectionId(SqlConnection conn, int evectionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[EvectionId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[LeaderId], ");
            builder.Append("\t[EvectionDest], ");
            builder.Append("\t[EvectionDate1], ");
            builder.Append("\t[EvectionDate2], ");
            builder.Append("\t[Status], ");
            builder.Append("\t[Allow], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[NotReason] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Evection] ");
            builder.Append("WHERE ");
            builder.Append("\t[EvectionId] = @EvectionId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@EvectionId", SqlDbType.Int) };
            commandParameters[0].Value = evectionId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Evection";
            return set.Tables[0];
        }

        public DataTable SelectHistory(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Evection].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Evection], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Evection].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Evection].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND [Attend_Evection].[Allow] IN(1) ");
            builder.Append("\tAND Year([Attend_Evection].[EvectionDate1]) = Year(GETDATE()) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Evection].[EvectionId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Evection";
            return set.Tables[0];
        }

        public DataTable SelectMy(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Evection].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Evection], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Evection].[UserId] = @UserId ");
            builder.Append("\tAND [Attend_Evection].[LeaderId] = [Users].[UserId] ");
            builder.Append("\tAND ([Attend_Evection].[Allow] IN(0,2) OR [Attend_Evection].[Status] IN(0)) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Attend_Evection].[EvectionId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Evection";
            return set.Tables[0];
        }
    }
}

